package dao;

import java.sql.*;
import java.util.*;

import bean.Customer;

public class MySql {
	
	public static void main(String args[]){
		try{
			new MySql().deleteNextCust(2);;
			System.out.println("ok");
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public List<String[]> getWaitLists() throws Exception {
		Connection conn = null;
        Statement stmt = null;
		ResultSet rs = null;
		List<String[]> list = new ArrayList<String[]>();
		try{
			conn = getConnection1();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select id, title from wait_list where wait_owner_id = 1 and active");
			while(rs.next()){
				String cols[] = new String[2];
				cols[0] = rs.getString(1);
				cols[1] = rs.getString(2);
				list.add(cols);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
				}
			}
		}
		return list;
	}
	
	public List<String> getWaitCusts(int id) throws Exception {
		Connection conn = null;
        PreparedStatement pst = null;
		ResultSet rs = null;
		List<String> list = new ArrayList<String>();
		try{
			conn = getConnection1();
			String sql = "select name from wait_cust where wait_list_id = ? order by id";
			pst = conn.prepareStatement(sql);
	        pst.setInt(1,id);
			rs = pst.executeQuery();
			while(rs.next()){
				list.add(rs.getString(1));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (pst != null) {
				try {
					pst.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
				}
			}
		}
		return list;
	}

	public void saveWaitCust(Customer cust) throws Exception {
		Connection conn = null;
        PreparedStatement pst = null;
		ResultSet rs = null;
		try{
			conn = getConnection1();
			String sql = "insert into wait_cust(wait_list_id, name, email) values(?,?,?)";
			pst = conn.prepareStatement(sql);
	        pst.setInt(1,cust.getWaitListId());
	        pst.setString(2,cust.getName());
	        pst.setString(3,cust.getEmail());
			pst.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (pst != null) {
				try {
					pst.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
				}
			}
		}
	}

	public void deleteNextCust(int id) throws Exception {
		Connection conn = null;
        PreparedStatement pst = null;
		ResultSet rs = null;
		try{
			conn = getConnection1();
			String sql = "delete from wait_cust where wait_list_id = ? order by id limit 1";
			pst = conn.prepareStatement(sql);
	        pst.setInt(1,id);
			pst.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
			}
			if (pst != null) {
				try {
					pst.close();
				} catch (Exception e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
				}
			}
		}
	}

	private Connection getConnection1()throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection("jdbc:mysql://trainingj.db.9050498.hostedresource.com/trainingj?" +
                  "user=trainingj&password=Black111!!!");
	}
	
}
